package ru.miit.elearning.vtutor.data;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import org.json.JSONArray;
import org.json.JSONException;

public class DataMiner {

    public DataMiner() throws Exception {
        // TODO: Move THROWS TO method
        try {
            state(DataMiner.JNDI_NAME);
        } catch (Exception ex) {
            log(ex);
            ex.printStackTrace();

            throw ex;
        }
        //prepareCheckApprovedStatement();
    }
    public static final String // COURSE_TABLE = "Course",
            // STUDENT_TABLE = "Student" ,
            //, LEARNS_TABLE = "Learns_course"
            LAB_TABLE = "lab", OBJECT_TABLE = "check_object", OBJECT_TYPE_TABLE = "check_object_type", WORKS_TABLE = "works_on_lab";
    public static final String CHECK_OBJECT_SEQUENCE = "check_object_id_sequence" //                                , WORKS_ON_LAB_SEQUENCE = "works_on_lab_id_sequence"
            ;
    //public static final String JNDI_NAME = "java:comp/env/Tutor";
    public static final String JNDI_NAME = "jdbc/virtualtutor";
    private Statement stmt;
    private Connection conn;

    protected void state(String name) throws ClassNotFoundException, SQLException, NamingException {
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup(name);

        conn = ds.getConnection();
        stmt = conn.createStatement();
    }
    protected PreparedStatement checkObjectsPS = null, updateCheckObjectsPS = null, addCheckObjectsPS = null, updateWorksOnLabPS = null, addWorksOnLabPS = null //, checkWorksOnLabExistsPS = null
            ;

    protected PreparedStatement getCheckObjectsPreparedStatement() throws SQLException {
        if (checkObjectsPS == null) {
            checkObjectsPS = conn.prepareStatement(
                    "SELECT * FROM " + OBJECT_TABLE + " WHERE id_course = ? AND lab_number = ?");
        }
        return checkObjectsPS;
    }

    protected PreparedStatement getUpdateCheckObjectsPS() throws SQLException {
        if (updateCheckObjectsPS == null) {
            updateCheckObjectsPS = conn.prepareStatement(
                    "UPDATE " + OBJECT_TABLE
                    + " SET path = ? , id_object_type = ? , content = ?"
                    + "WHERE id_object = ?");
        }
        return updateCheckObjectsPS;
    }

    protected PreparedStatement getAddCheckObjectsPS() throws SQLException {
        if (addCheckObjectsPS == null) {
            addCheckObjectsPS = conn.prepareStatement(
                    "INSERT INTO " + OBJECT_TABLE + " (path, id_object_type, content, id_course, lab_number, id_object) "
                    + "VALUES(?, ?, ?, ?, ?, ?)");
        }
        return addCheckObjectsPS;
    }

//    protected PreparedStatement getCheckWorksOnLabExistsPS() throws SQLException {
//        if (checkWorksOnLabExistsPS == null) {
//            checkWorksOnLabExistsPS = conn.prepareStatement("SELECT 1 FROM " + WORKS_TABLE + " WHERE id_student = ? AND id_course = ? AND lab_number = ?");
//        }
//        return checkWorksOnLabExistsPS;
//    }
    protected PreparedStatement getUpdateWorksOnLabPS() throws SQLException {
        if (updateWorksOnLabPS == null) {
            updateWorksOnLabPS = conn.prepareStatement(
                    "UPDATE " + WORKS_TABLE + " SET finished = ?, details = ?, check_date = ? "
                    + "WHERE id_student = ? AND id_course = ? AND lab_number = ?");
        }
        return updateWorksOnLabPS;
    }

    protected PreparedStatement getAddWorksOnLabPS() throws SQLException {
        if (addWorksOnLabPS == null) {
            addWorksOnLabPS = conn.prepareStatement(
                    "INSERT INTO " + WORKS_TABLE + " (id_student, id_course, lab_number, finished, details, check_date) "
                    + "VALUES(?, ?, ?, ?, ?, ?)");
        }
        return addWorksOnLabPS;
    }

    public void setFinishedByWork(int studentId, int courseId, int labNumber, int finished, String details, Date checkDate) throws SQLException {
        PreparedStatement ps = getUpdateWorksOnLabPS();

        ps.setInt(1, finished);
        ps.setString(2, details);
        ps.setDate(3, checkDate);

        ps.setInt(4, studentId);
        ps.setInt(5, courseId);
        ps.setInt(6, labNumber);

        if (ps.executeUpdate() == 0) {
            ResultSet rs = stmt.executeQuery("SELECT 1 FROM " + LAB_TABLE + " WHERE id_course = " + courseId + " AND lab_number = " + labNumber);
            if (!rs.next()) {
                stmt.executeUpdate("INSERT INTO " + LAB_TABLE + " (id_course, lab_number) VALUES (" + courseId + ", " + labNumber + ")");
            }

            PreparedStatement addPS = getAddWorksOnLabPS();

            addPS.setInt(1, studentId);
            addPS.setInt(2, courseId);
            addPS.setInt(3, labNumber);
            addPS.setInt(4, finished);
            addPS.setString(5, details);
            addPS.setDate(6, checkDate);

            addPS.executeUpdate();
        }
    }

    protected long generateCheckObjectKey() throws SQLException {
        ResultSet rs = null;
        try {
            rs = stmt.executeQuery("SELECT nextval('" + CHECK_OBJECT_SEQUENCE + "')");
            rs.next();
            return rs.getLong("nextval");
        } finally {
            if (rs != null) {
                rs.close();
            }
        }
    }

    public List<CheckObject> getCheckObjectList(int courseId, int labNumber) throws SQLException {
        PreparedStatement ps = getCheckObjectsPreparedStatement();
        ps.setInt(1, courseId);
        ps.setInt(2, labNumber);

        ResultSet rs = ps.executeQuery();
        List<CheckObject> checkObjectList = new ArrayList<CheckObject>();
        while (rs.next()) {
            CheckObject co = new CheckObject();
            co.setId(rs.getInt("id_object"));
            co.setType(rs.getInt("id_object_type"));
            co.setPath(rs.getString("path"));

            String contents = rs.getString("content");
            if (contents != null) {
                co.setContent(contents.split(","));
            }

            checkObjectList.add(co);
        }

        return checkObjectList;
    }

    public Map<Integer, Integer> findPercentByUserByCourse(int courseId, int studentId) throws SQLException {
        ResultSet rs = stmt.executeQuery("SELECT lab_number, finished FROM " + WORKS_TABLE + " "
                + "WHERE id_student = " + studentId + " AND id_course = " + courseId + " ORDER BY lab_number");

        Map<Integer, Integer> map = new HashMap<Integer, Integer>();
        while (rs.next()) {
            map.put(rs.getInt("lab_number"), rs.getInt("finished"));
        }
        return map;
    }

    public String findWorkDetails(int studentId, int courseId, int labNumber) throws SQLException {
        ResultSet rs = stmt.executeQuery("SELECT details FROM " + WORKS_TABLE + " WHERE id_student = " + studentId + " AND id_course = " + courseId + " AND lab_number = " + labNumber);
        if (rs.next()) {
            return rs.getString("Details");
        } else {
            return null;
        }
    }

    public void updateCheckObject(Integer coId, String path, int type, JSONArray combsArray) throws SQLException, JSONException {
        PreparedStatement ps = getUpdateCheckObjectsPS();
        ps.setString(1, path);
        ps.setInt(2, type);

        if (combsArray != null) {
            StringBuffer sb = new StringBuffer();
            for (int i = 0; i < combsArray.length() - 1; i++) {
                sb.append(combsArray.get(i).toString() + ",");
            }
            sb.append(combsArray.get(combsArray.length() - 1).toString());

            ps.setString(3, sb.toString());
        } else {
            ps.setString(3, null);
        }

        ps.setInt(4, coId);

        ps.executeUpdate();
    }

    public int addCheckObject(int courseId, int labNumber, String path, int type, JSONArray combsArray) throws SQLException, JSONException {
        PreparedStatement ps = getAddCheckObjectsPS();
        ps.setString(1, path);
        ps.setInt(2, type);

        if (combsArray != null) {
            StringBuffer sb = new StringBuffer();
            for (int i = 0; i < combsArray.length() - 1; i++) {
                sb.append(combsArray.get(i).toString() + ",");
            }
            sb.append(combsArray.get(combsArray.length() - 1).toString());

            ps.setString(3, sb.toString());
        } else {
            ps.setString(3, null);
        }

        ps.setInt(4, courseId);
        ps.setInt(5, labNumber);

        int newId = (int) generateCheckObjectKey();
        ps.setInt(6, newId);

        ps.executeUpdate();
        return newId;
    }

    public void deleteCheckObject(int coId) throws SQLException {
        stmt.executeUpdate("DELETE FROM " + OBJECT_TABLE + " WHERE id_object = " + coId);
    }

    public Set<Integer> getCheckObjectsIdSet(int courseId, int labNumber) throws SQLException {
        ResultSet rs = stmt.executeQuery("SELECT id_object FROM " + OBJECT_TABLE + " WHERE id_course = " + courseId + " and lab_number = " + labNumber);
        Set<Integer> allKeysSet = new HashSet<Integer>();
        while (rs.next()) {
            allKeysSet.add(rs.getInt(1));
        }
        return allKeysSet;
    }

    public List<CheckObjectType> getCheckObjectTypeList() throws SQLException {
        List<CheckObjectType> coTypeList = null;
        ResultSet rs = stmt.executeQuery("SELECT * FROM " + OBJECT_TYPE_TABLE + " ORDER BY id_object_type");
        if (rs != null) {
            coTypeList = new ArrayList<CheckObjectType>();
            while (rs.next()) {
                CheckObjectType coType = new CheckObjectType();
                coType.setId(rs.getInt("id_object_type"));
                coType.setObjectType(rs.getString("object_type"));

                coTypeList.add(coType);
            }
        }
        return coTypeList;
    }

    public List<Integer> getLabNumbersByCourse(int courseId) {
        List labNumberList = null;

        try {
            ResultSet rs = stmt.executeQuery("SELECT lab_number FROM " + LAB_TABLE + " WHERE id_course = " + courseId + " ORDER BY lab_number");
            if (rs != null) {
                labNumberList = new ArrayList();
                while (rs.next()) {
                    labNumberList.add(rs.getInt(1));
                }
            }
        } catch (Exception e) {
            log(e);
            e.printStackTrace();
        }

        return labNumberList;
    }

    private void log(Exception e) {
        Logger.getLogger(DataMiner.class.getName()).log(Level.SEVERE, null, e);
    }

    public void addLab(int courseId, int labNumber) throws SQLException {
        stmt.executeUpdate("INSERT INTO " + LAB_TABLE + " (id_course, lab_number) "
                + "VALUES (" + courseId + ", " + labNumber + ")");
    }

    public void deleteLab(int courseId, int labNumber) throws SQLException {
        stmt.executeUpdate("DELETE FROM " + LAB_TABLE + " WHERE id_course = " + courseId + " AND lab_number = " + labNumber);
    }

    public void close() {
        if (stmt != null) {
            try {
                stmt.close();
//                System.out.println("stmt closed");
            } catch (SQLException ex) {
                Logger.getLogger(DataMiner.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        if (conn != null) {
            try {
                conn.close();
//                System.out.println("conn closed");
            } catch (SQLException ex) {
                log(ex);
                System.err.println("Failed to close connection. Message: " + ex.getMessage());
            }
        }
    }
}
